-- EXEC SP_CALCULATE_STEP_COST_BYPRODUCT
CREATE PROC SP_CALCULATE_STEP_COST_BYPRODUCT 
AS
BEGIN
	
	DECLARE @LOT_ID			BIGINT
	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @PS_TYPE_NAME	VARCHAR(50) 
	DECLARE @QTY_IN			INT
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR			INT
	DECLARE @WAFER_QTY_IN	INT
	DECLARE @CUMM_STD_COST	NUMERIC(18,4) 
	
	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE

		
	DECLARE LOT_CURSOR CURSOR FOR 
 	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, WAFER_QTY_IN, QTY_IN, PS_TYPE_NAME FROM LOT_TABLE LT, PROCESS_STEP PS
	WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_CURRENT_STATUS != 'Close'
	union
	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, WAFER_QTY_IN, QTY_IN, PS_TYPE_NAME FROM SHIPPED_LOT SL,LOT_TABLE LT, PROCESS_STEP PS
	WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS != 'CLOSE'

	OPEN LOT_CURSOR
	FETCH NEXT FROM LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE_NAME
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SELECT @CUMM_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR

		IF ( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT')
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = @CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
		END
		ELSE 
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = @CUMM_STD_COST, LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
		END

		SET @CUMM_STD_COST	= NULL 
		SET @LOT_ID			= NULL		
		SET @PROCESSSTEP_ID = NULL
		SET @WAFER_QTY_IN	= NULL
		SET @QTY_IN			= NULL
		SET @PS_TYPE_NAME	= NULL

		FETCH NEXT FROM LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE_NAME
	END	
	CLOSE LOT_CURSOR
	DEALLOCATE LOT_CURSOR
END 